package com.cvit.service;

import java.io.BufferedReader;
import java.io.DataOutputStream;
import java.io.File;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.cvit.dao.StudentMapper;
import com.cvit.model.ExcelBean;
import com.cvit.model.Student;
import com.cvit.model.StudentExcel;
import com.cvit.utils.Base64;
import com.cvit.utils.ExcelUtil;
import com.cvit.utils.FileToZip;
import com.cvit.utils.FileUpload;
import com.cvit.utils.Utils;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

@Service
public class StudentService {

	@Autowired
	StudentMapper studentMapper;

	/**
	 * 根据条件查询
	 */
	public List<Student> getStudnets(Student student) {
		// TODO Auto-generated method stub
		List<Student> students = studentMapper.selectByStudent(student);
		return students;
	}

	/**
	 * 根据Id查询
	 */
	public Student getStudnetById(String id) {
		// TODO Auto-generated method stub
		Student students = studentMapper.selectByPrimaryKey(id);
		return students;
	}

	/**
	 * 根据考生号查询
	 */
	public Student getStudnetByEamineeId(String eamineeId) {
		// TODO Auto-generated method stub
		Student students = studentMapper.selectByEamineeId(eamineeId);
		return students;
	}

	/**
	 * 根据身份证号查询
	 */
	public Student getStudnetByCardId(String cardId) {
		// TODO Auto-generated method stub
		Student students = studentMapper.selectByCardId(cardId);
		return students;
	}

	/**
	 * 根据身份证号和学生号查询
	 */
	public Student selectByCardIdAndEamineeId(String cardId, String eamineeId) {
		Student student = new Student();
		student.setEamineeId(eamineeId);
		student.setCardId(cardId);
		Student students = studentMapper.selectVerify(student);// 调用验证查询方法
		return students;
	}

	/**
	 * 注册学生信息
	 */
	public String addStudent(Student student, HttpServletRequest request) {
		// TODO Auto-generated method stub
		String id = JudgeIdRepeat();
		String cardId = student.getCardId();
		if (cardId.indexOf('x') != -1) {// 把x转成X
			cardId = cardId.replace("x", "X");
		}
		student.setCardId(cardId);
		String birthday = Utils.cardBirthday(cardId);
		int gender = Utils.cardGender(cardId);
		if(student.getApplyType() == 1 ||student.getApplyType() == 3 || student.getApplyType() == 4 ){
			student.setApplyType(1);
		}else{
			student.setApplyType(2);
		}
		student.setId(id);
		student.setGraduationType(student.getGraduationType()+1);
		student.setBirthday(birthday);
		student.setGender(gender);
		student.setDelType(0);
		student.setRegisterTime(new Date());
		student.setLanguages("英语");
		studentMapper.insert(student);
		return id;
	}

	/**
	 * 根据考生号修改缴费状态为1
	 */
	public Integer updatePayByEamineeId(String student_id) {
		// TODO Auto-generated method stub
		Student student = new Student();
		student.setPayType(1);
		student.setId(student_id);
		Integer type = studentMapper.updatePayByEamineeId(student);
		return type;// 成功返回1 失败返回0
	}

	/**
	 * 根据考生号修改打印状态
	 */
	public Integer updateTicketByEamineeId(String eamineeId) {
		// TODO Auto-generated method stub
		Student student = new Student();
		student.setTicketType(1);
		student.setEamineeId(eamineeId);
		int type = studentMapper.updateTicketByEamineeId(student);
		return type;// 成功返回1 失败返回0
	}

	/**
	 * 修改学生信息
	 */
	public void updateStudent(Student student) {
		// TODO Auto-generated method stub
		Student students = studentMapper.selectByPrimaryKey(student.getId());
		String volunteer = students.getVolunteerOne();
		Integer prooiss = students.getPromiseType();
		Integer proois = student.getPromiseType();
		if (proois == null) {
			proois = 3;
		}
		Integer pays = students.getPayType();
		Integer pay = student.getPayType();
		if (pay == null) {
			pay = 3;
		}
		Integer tickets = students.getTicketType();
		Integer ticket = student.getTicketType();
		if (ticket == null) {
			ticket = 3;
		}
		if (volunteer == null || volunteer == "") {// 志愿为空时PromiseType、PayType、TicketType默认值2
			student.setPromiseType(2);
			student.setPayType(2);
			student.setTicketType(2);
			student.setAdjustType(2);
		}
		if (proois == 1 && prooiss == 2) {//签署承诺书
			student.setPromiseTime(new Date());
		}
		if (proois == 1 && prooiss == 1) {//修改信息时再次签署承诺书 更新承诺时间
			student.setPromiseTime(new Date());
		}
		if (pay == 1 && pays == 2) {
			student.setPayTime(new Date());
		}
		if (ticket == 1 && tickets == 2) {
			student.setTicketTime(new Date());
		}
		studentMapper.updateByPrimaryKeySelective(student);
	}

	/**
	 * 身份验证
	 */
	public String verifyStudent(String eamineeId, String cardId) {
		// TODO Auto-generated method stub
		String id = null;
		Student student = new Student();
		student.setEamineeId(eamineeId);
		student.setCardId(cardId);
		Student students = studentMapper.selectVerify(student);
		if (students == null) {
			return id;
		} else {
			id = students.getId();
			return id;
		}
	}

	/**
	 * 导出Excel
	 * 
	 * @throws Exception
	 */

	public XSSFWorkbook exportExcelInfo(String studentData) throws Exception {
		// 根据条件查询数据，把数据装载到一个list中
		List<Student> list = studentMapper.selectByStudent(null);
		List<StudentExcel> lists = new ArrayList<StudentExcel>();
		for (int i = 0; i < list.size(); i++) {
			Student student = list.get(i);
			StudentExcel studentExcel = new StudentExcel();
			if (student.getGender() == 1) {// 把int类型转成String
				studentExcel.setGender("男");
			} else {
				studentExcel.setGender("女");
			}
			if (student.getExamineeType() == 1) {
				studentExcel.setExamineeType("城镇应届");
			} else if (student.getExamineeType() == 2) {
				studentExcel.setExamineeType("农村应届");
			} else if (student.getExamineeType() == 3) {
				studentExcel.setExamineeType("城镇往届");
			} else {
				studentExcel.setExamineeType("农村往届");
			} 
			if (student.getGraduationType() == 1) {
				studentExcel.setGraduationType("高中");
			} else if (student.getGraduationType() == 2) {
				studentExcel.setGraduationType("中等师范");
			} else if (student.getGraduationType() == 3) {
				studentExcel.setGraduationType("中等专业学校");
			} else if (student.getGraduationType() == 4) {
				studentExcel.setGraduationType("职业高中学校");
			} else if(student.getGraduationType() == 5){
				studentExcel.setGraduationType("技工学校");
			}else{
				studentExcel.setGraduationType("其他学校");
			}
			if (student.getApplyType() == 1) {
				studentExcel.setApplyType("文史");
			} else if (student.getApplyType() == 2) {
				studentExcel.setApplyType("理工");
			} 
			if (student.getTestType() == 1) {
				studentExcel.setTestType("面试");
			} else {
				studentExcel.setTestType("机考");
			}
			if (student.getPromiseType() == 1) {
				studentExcel.setPromiseType("同意");
			} else {
				studentExcel.setPromiseType("不同意");
			}
			if (student.getPayType() == 1) {
				studentExcel.setPayType("已缴费");
			} else {
				studentExcel.setPayType("未缴费");
			}
			if (student.getTicketType() == 1) {
				studentExcel.setTicketType("已打印");
			} else {
				studentExcel.setTicketType("未打印");
			}
			if (student.getAdjustType() == 1) {
				studentExcel.setAdjustType("通过审核");
			} else {
				studentExcel.setAdjustType("未通过审核");
			}

			studentExcel.setId(student.getId());
			studentExcel.setHeight(student.getHeight());
			studentExcel.setEamineeId(student.getEamineeId());
			studentExcel.setCardId(student.getCardId());
			studentExcel.setName(student.getName());
			studentExcel.setPassword(student.getPassword());
			studentExcel.setBirthday(student.getBirthday());
			studentExcel.setImage(student.getImage());
			studentExcel.setNation(student.getNation());
			studentExcel.setLanguages(student.getLanguages());
			studentExcel.setProvinces(student.getProvinces());
			studentExcel.setRegions(student.getRegions());
			studentExcel.setCity(student.getCity());
			studentExcel.setTelephone(student.getTelephone());
			studentExcel.setGradSchool(student.getGradSchool());
			studentExcel.setIntheclass(student.getIntheclass());
			studentExcel.setVolunteerOne(student.getVolunteerOne());
			studentExcel.setVolunteerTwo(student.getVolunteerTwo());
			studentExcel.setVolunteerThree(student.getVolunteerThree());
			studentExcel.setVolunteerFour(student.getVolunteerFour());
			studentExcel.setVolunteerFive(student.getVolunteerFive());
			studentExcel.setMailingAddress(student.getMailingAddress());
			studentExcel.setPostalCode(student.getPostalCode());
			studentExcel.setAddressee(student.getAddressee());
			studentExcel.setAddrTele(student.getAddrTele());
			studentExcel.setRegisterTime(student.getRegisterTime());
			studentExcel.setPayTime(student.getPayTime());
			studentExcel.setTicketTime(student.getTicketTime());
			studentExcel.setPromiseTime(student.getPromiseTime());
			studentExcel.setLanguages(student.getLanguages());
			lists.add(studentExcel);
		}

		List<ExcelBean> excel = new ArrayList<>();
		Map<Integer, List<ExcelBean>> map = new LinkedHashMap<>();
		XSSFWorkbook xssfWorkbook = null;
		// 设置标题栏
		excel.add(new ExcelBean("学生序号", "id", 0));
		excel.add(new ExcelBean("报考序号", "height", 0));
		excel.add(new ExcelBean("考生号", "eamineeId", 0));
		excel.add(new ExcelBean("身份证号", "cardId", 0));
		excel.add(new ExcelBean("姓名", "name", 0));
		excel.add(new ExcelBean("审核号", "password", 0));
		excel.add(new ExcelBean("生日", "birthday", 0));
		excel.add(new ExcelBean("照片", "image", 0));
		excel.add(new ExcelBean("性别", "gender", 0));
		excel.add(new ExcelBean("民族", "nation", 0));
		excel.add(new ExcelBean("外语语种", "languages", 0));
		excel.add(new ExcelBean("省", "provinces", 0));
		excel.add(new ExcelBean("地区", "regions", 0));
		excel.add(new ExcelBean("城市", "city", 0));
		excel.add(new ExcelBean("手机号", "telephone", 0));
		excel.add(new ExcelBean("毕业学校", "gradSchool", 0));
		excel.add(new ExcelBean("所在班级", "intheclass", 0));
		excel.add(new ExcelBean("考生类别", "examineeType", 0));
		excel.add(new ExcelBean("毕业类别", "graduationType", 0));
		excel.add(new ExcelBean("报考类别", "applyType", 0));
		excel.add(new ExcelBean("综合素质测试", "testType", 0));
		excel.add(new ExcelBean("第一志愿", "volunteerOne", 0));
		excel.add(new ExcelBean("第二志愿", "volunteerTwo", 0));
		excel.add(new ExcelBean("第三志愿", "volunteerThree", 0));
		excel.add(new ExcelBean("第四志愿", "volunteerFour", 0));
		excel.add(new ExcelBean("第五志愿", "volunteerFive", 0));
		excel.add(new ExcelBean("审核状态", "adjustType", 0));
		excel.add(new ExcelBean("邮寄地址", "mailingAddress", 0));
		excel.add(new ExcelBean("邮政编码", "postalCode", 0));
		excel.add(new ExcelBean("收件人", "addressee", 0));
		excel.add(new ExcelBean("收件人电话", "addrTele", 0));
		excel.add(new ExcelBean("承诺书状态", "promiseType", 0));
		excel.add(new ExcelBean("缴费状态", "payType", 0));
		excel.add(new ExcelBean("准考证打印状态", "ticketType", 0));
		excel.add(new ExcelBean("注册时间", "registerTime", 0));
		excel.add(new ExcelBean("缴费时间", "payTime", 0));
		excel.add(new ExcelBean("打印准考证时间", "ticketTime", 0));
		excel.add(new ExcelBean("承诺时间", "promiseTime", 0));
		map.put(0, excel);
		String sheetName = studentData;
		// 调用ExcelUtil的方法
		xssfWorkbook = ExcelUtil.createExcelFile(StudentExcel.class, lists, map, sheetName);
		return xssfWorkbook;
	}

	/**
	 * 导出Excel 已缴费
	 * 
	 * @throws Exception
	 */

	public XSSFWorkbook exportExcelInfoPay(String studentData) throws Exception {
		// 根据条件查询数据，把数据装载到一个list中
		Student students = new Student();
		students.setPayType(1);
		List<Student> list = studentMapper.selectByStudent(students);
		List<StudentExcel> lists = new ArrayList<StudentExcel>();
		for (int i = 0; i < list.size(); i++) {
			Student student = list.get(i);
			StudentExcel studentExcel = new StudentExcel();
			if (student.getGender() == 1) {// 把int类型转成String
				studentExcel.setGender("男");
			} else {
				studentExcel.setGender("女");
			}
			if (student.getExamineeType() == 1) {
				studentExcel.setExamineeType("城镇应届");
			} else if (student.getExamineeType() == 2) {
				studentExcel.setExamineeType("农村应届");
			} else if (student.getExamineeType() == 3) {
				studentExcel.setExamineeType("城镇往届");
			} else {
				studentExcel.setExamineeType("农村往届");
			} 
			if (student.getGraduationType() == 1) {
				studentExcel.setGraduationType("高中");
			} else if (student.getGraduationType() == 2) {
				studentExcel.setGraduationType("中等师范");
			} else if (student.getGraduationType() == 3) {
				studentExcel.setGraduationType("中等专业学校");
			} else if (student.getGraduationType() == 4) {
				studentExcel.setGraduationType("职业高中学校");
			} else if(student.getGraduationType() == 5){
				studentExcel.setGraduationType("技工学校");
			} else{
				studentExcel.setGraduationType("其他学校");
			}
			if (student.getApplyType() == 1) {
				studentExcel.setApplyType("文史");
			} else if (student.getApplyType() == 2) {
				studentExcel.setApplyType("理工");
			} 
			if (student.getTestType() == 1) {
				studentExcel.setTestType("面试");
			} else {
				studentExcel.setTestType("机考");
			}
			if (student.getPromiseType() == 1) {
				studentExcel.setPromiseType("同意");
			} else {
				studentExcel.setPromiseType("不同意");
			}
			if (student.getPayType() == 1) {
				studentExcel.setPayType("已缴费");
			} else {
				studentExcel.setPayType("未缴费");
			}
			if (student.getTicketType() == 1) {
				studentExcel.setTicketType("已打印");
			} else {
				studentExcel.setTicketType("未打印");
			}
			if (student.getAdjustType() == 1) {
				studentExcel.setAdjustType("通过审核");
			} else {
				studentExcel.setAdjustType("未通过审核");
			}

			studentExcel.setId(student.getId());
			studentExcel.setHeight(student.getHeight());
			studentExcel.setEamineeId(student.getEamineeId());
			studentExcel.setCardId(student.getCardId());
			studentExcel.setName(student.getName());
			studentExcel.setPassword(student.getPassword());
			studentExcel.setBirthday(student.getBirthday());
			studentExcel.setImage(student.getImage());
			studentExcel.setNation(student.getNation());
			studentExcel.setLanguages(student.getLanguages());
			studentExcel.setProvinces(student.getProvinces());
			studentExcel.setRegions(student.getRegions());
			studentExcel.setCity(student.getCity());
			studentExcel.setTelephone(student.getTelephone());
			studentExcel.setGradSchool(student.getGradSchool());
			studentExcel.setIntheclass(student.getIntheclass());
			studentExcel.setVolunteerOne(student.getVolunteerOne());
			studentExcel.setVolunteerTwo(student.getVolunteerTwo());
			studentExcel.setVolunteerThree(student.getVolunteerThree());
			studentExcel.setVolunteerFour(student.getVolunteerFour());
			studentExcel.setVolunteerFive(student.getVolunteerFive());
			studentExcel.setMailingAddress(student.getMailingAddress());
			studentExcel.setPostalCode(student.getPostalCode());
			studentExcel.setAddressee(student.getAddressee());
			studentExcel.setAddrTele(student.getAddrTele());
			studentExcel.setRegisterTime(student.getRegisterTime());
			studentExcel.setPayTime(student.getPayTime());
			studentExcel.setTicketTime(student.getTicketTime());
			studentExcel.setPromiseTime(student.getPromiseTime());
			studentExcel.setLanguages(student.getLanguages());
			lists.add(studentExcel);
		}

		List<ExcelBean> excel = new ArrayList<>();
		Map<Integer, List<ExcelBean>> map = new LinkedHashMap<>();
		XSSFWorkbook xssfWorkbook = null;
		// 设置标题栏
		excel.add(new ExcelBean("学生序号", "id", 0));
		excel.add(new ExcelBean("报考序号", "height", 0));
		excel.add(new ExcelBean("考生号", "eamineeId", 0));
		excel.add(new ExcelBean("身份证号", "cardId", 0));
		excel.add(new ExcelBean("姓名", "name", 0));
		excel.add(new ExcelBean("审核号", "password", 0));
		excel.add(new ExcelBean("生日", "birthday", 0));
		excel.add(new ExcelBean("照片", "image", 0));
		excel.add(new ExcelBean("性别", "gender", 0));
		excel.add(new ExcelBean("民族", "nation", 0));
		excel.add(new ExcelBean("外语语种", "languages", 0));
		excel.add(new ExcelBean("省", "provinces", 0));
		excel.add(new ExcelBean("地区", "regions", 0));
		excel.add(new ExcelBean("城市", "city", 0));
		excel.add(new ExcelBean("手机号", "telephone", 0));
		excel.add(new ExcelBean("毕业学校", "gradSchool", 0));
		excel.add(new ExcelBean("所在班级", "intheclass", 0));
		excel.add(new ExcelBean("考生类别", "examineeType", 0));
		excel.add(new ExcelBean("毕业类别", "graduationType", 0));
		excel.add(new ExcelBean("报考类别", "applyType", 0));
		excel.add(new ExcelBean("综合素质测试", "testType", 0));
		excel.add(new ExcelBean("第一志愿", "volunteerOne", 0));
		excel.add(new ExcelBean("第二志愿", "volunteerTwo", 0));
		excel.add(new ExcelBean("第三志愿", "volunteerThree", 0));
		excel.add(new ExcelBean("第四志愿", "volunteerFour", 0));
		excel.add(new ExcelBean("第五志愿", "volunteerFive", 0));
		excel.add(new ExcelBean("审核状态", "adjustType", 0));
		excel.add(new ExcelBean("邮寄地址", "mailingAddress", 0));
		excel.add(new ExcelBean("邮政编码", "postalCode", 0));
		excel.add(new ExcelBean("收件人", "addressee", 0));
		excel.add(new ExcelBean("收件人电话", "addrTele", 0));
		excel.add(new ExcelBean("承诺书状态", "promiseType", 0));
		excel.add(new ExcelBean("缴费状态", "payType", 0));
		excel.add(new ExcelBean("准考证打印状态", "ticketType", 0));
		excel.add(new ExcelBean("注册时间", "registerTime", 0));
		excel.add(new ExcelBean("缴费时间", "payTime", 0));
		excel.add(new ExcelBean("打印准考证时间", "ticketTime", 0));
		excel.add(new ExcelBean("承诺时间", "promiseTime", 0));
		map.put(0, excel);
		String sheetName = studentData;
		// 调用ExcelUtil的方法
		xssfWorkbook = ExcelUtil.createExcelFile(StudentExcel.class, lists, map, sheetName);
		return xssfWorkbook;
	}

	/**
	 * 导出Excel 已审核
	 * 
	 * @throws Exception
	 */

	public XSSFWorkbook exportExcelInfoA(String studentData) throws Exception {
		// 根据条件查询数据，把数据装载到一个list中
		Student students = new Student();
		students.setAdjustType(1);
		List<Student> list = studentMapper.selectByStudent(students);
		List<StudentExcel> lists = new ArrayList<StudentExcel>();
		for (int i = 0; i < list.size(); i++) {
			Student student = list.get(i);
			StudentExcel studentExcel = new StudentExcel();
			if (student.getGender() == 1) {// 把int类型转成String
				studentExcel.setGender("男");
			} else {
				studentExcel.setGender("女");
			}
			if (student.getExamineeType() == 1) {
				studentExcel.setExamineeType("城镇应届");
			} else if (student.getExamineeType() == 2) {
				studentExcel.setExamineeType("农村应届");
			} else if (student.getExamineeType() == 3) {
				studentExcel.setExamineeType("城镇往届");
			} else {
				studentExcel.setExamineeType("农村往届");
			} 
			if (student.getGraduationType() == 1) {
				studentExcel.setGraduationType("高中");
			} else if (student.getGraduationType() == 2) {
				studentExcel.setGraduationType("中等师范");
			} else if (student.getGraduationType() == 3) {
				studentExcel.setGraduationType("中等专业学校");
			} else if (student.getGraduationType() == 4) {
				studentExcel.setGraduationType("职业高中学校");
			} else if(student.getGraduationType() == 5){
				studentExcel.setGraduationType("技工学校");
			} else{
				studentExcel.setGraduationType("其他学校");
			}
			if (student.getApplyType() == 1) {
				studentExcel.setApplyType("文史");
			} else if (student.getApplyType() == 2) {
				studentExcel.setApplyType("理工");
			} 
			if (student.getTestType() == 1) {
				studentExcel.setTestType("面试");
			} else {
				studentExcel.setTestType("机考");
			}
			if (student.getPromiseType() == 1) {
				studentExcel.setPromiseType("同意");
			} else {
				studentExcel.setPromiseType("不同意");
			}
			if (student.getPayType() == 1) {
				studentExcel.setPayType("已缴费");
			} else {
				studentExcel.setPayType("未缴费");
			}
			if (student.getTicketType() == 1) {
				studentExcel.setTicketType("已打印");
			} else {
				studentExcel.setTicketType("未打印");
			}
			if (student.getAdjustType() == 1) {
				studentExcel.setAdjustType("通过审核");
			} else {
				studentExcel.setAdjustType("未通过审核");
			}

			studentExcel.setId(student.getId());
			studentExcel.setHeight(student.getHeight());
			studentExcel.setEamineeId(student.getEamineeId());
			studentExcel.setCardId(student.getCardId());
			studentExcel.setName(student.getName());
			studentExcel.setPassword(student.getPassword());
			studentExcel.setBirthday(student.getBirthday());
			studentExcel.setImage(student.getImage());
			studentExcel.setNation(student.getNation());
			studentExcel.setLanguages(student.getLanguages());
			studentExcel.setProvinces(student.getProvinces());
			studentExcel.setRegions(student.getRegions());
			studentExcel.setCity(student.getCity());
			studentExcel.setTelephone(student.getTelephone());
			studentExcel.setGradSchool(student.getGradSchool());
			studentExcel.setIntheclass(student.getIntheclass());
			studentExcel.setVolunteerOne(student.getVolunteerOne());
			studentExcel.setVolunteerTwo(student.getVolunteerTwo());
			studentExcel.setVolunteerThree(student.getVolunteerThree());
			studentExcel.setVolunteerFour(student.getVolunteerFour());
			studentExcel.setVolunteerFive(student.getVolunteerFive());
			studentExcel.setMailingAddress(student.getMailingAddress());
			studentExcel.setPostalCode(student.getPostalCode());
			studentExcel.setAddressee(student.getAddressee());
			studentExcel.setAddrTele(student.getAddrTele());
			studentExcel.setRegisterTime(student.getRegisterTime());
			studentExcel.setPayTime(student.getPayTime());
			studentExcel.setTicketTime(student.getTicketTime());
			studentExcel.setPromiseTime(student.getPromiseTime());
			studentExcel.setLanguages(student.getLanguages());
			lists.add(studentExcel);
		}

		List<ExcelBean> excel = new ArrayList<>();
		Map<Integer, List<ExcelBean>> map = new LinkedHashMap<>();
		XSSFWorkbook xssfWorkbook = null;
		// 设置标题栏
		excel.add(new ExcelBean("学生序号", "id", 0));
		excel.add(new ExcelBean("报考序号", "height", 0));
		excel.add(new ExcelBean("考生号", "eamineeId", 0));
		excel.add(new ExcelBean("身份证号", "cardId", 0));
		excel.add(new ExcelBean("姓名", "name", 0));
		excel.add(new ExcelBean("审核号", "password", 0));
		excel.add(new ExcelBean("生日", "birthday", 0));
		excel.add(new ExcelBean("照片", "image", 0));
		excel.add(new ExcelBean("性别", "gender", 0));
		excel.add(new ExcelBean("民族", "nation", 0));
		excel.add(new ExcelBean("外语语种", "languages", 0));
		excel.add(new ExcelBean("省", "provinces", 0));
		excel.add(new ExcelBean("地区", "regions", 0));
		excel.add(new ExcelBean("城市", "city", 0));
		excel.add(new ExcelBean("手机号", "telephone", 0));
		excel.add(new ExcelBean("毕业学校", "gradSchool", 0));
		excel.add(new ExcelBean("所在班级", "intheclass", 0));
		excel.add(new ExcelBean("考生类别", "examineeType", 0));
		excel.add(new ExcelBean("毕业类别", "graduationType", 0));
		excel.add(new ExcelBean("报考类别", "applyType", 0));
		excel.add(new ExcelBean("综合素质测试", "testType", 0));
		excel.add(new ExcelBean("第一志愿", "volunteerOne", 0));
		excel.add(new ExcelBean("第二志愿", "volunteerTwo", 0));
		excel.add(new ExcelBean("第三志愿", "volunteerThree", 0));
		excel.add(new ExcelBean("第四志愿", "volunteerFour", 0));
		excel.add(new ExcelBean("第五志愿", "volunteerFive", 0));
		excel.add(new ExcelBean("审核状态", "adjustType", 0));
		excel.add(new ExcelBean("邮寄地址", "mailingAddress", 0));
		excel.add(new ExcelBean("邮政编码", "postalCode", 0));
		excel.add(new ExcelBean("收件人", "addressee", 0));
		excel.add(new ExcelBean("收件人电话", "addrTele", 0));
		excel.add(new ExcelBean("承诺书状态", "promiseType", 0));
		excel.add(new ExcelBean("缴费状态", "payType", 0));
		excel.add(new ExcelBean("准考证打印状态", "ticketType", 0));
		excel.add(new ExcelBean("注册时间", "registerTime", 0));
		excel.add(new ExcelBean("缴费时间", "payTime", 0));
		excel.add(new ExcelBean("打印准考证时间", "ticketTime", 0));
		excel.add(new ExcelBean("承诺时间", "promiseTime", 0));
		map.put(0, excel);
		String sheetName = studentData;
		// 调用ExcelUtil的方法
		xssfWorkbook = ExcelUtil.createExcelFile(StudentExcel.class, lists, map, sheetName);
		return xssfWorkbook;
	}
	
	/**
	 * 导出Excel 未缴费
	 * 
	 * @throws Exception
	 */
	public XSSFWorkbook exportExcelInfoB(String studentData) throws Exception {
		// 根据条件查询数据，把数据装载到一个list中
		Student students = new Student();
		List<Student> list = studentMapper.selectByPay(null);
		List<StudentExcel> lists = new ArrayList<StudentExcel>();
		for (int i = 0; i < list.size(); i++) {
			Student student = list.get(i);
			StudentExcel studentExcel = new StudentExcel();
			if (student.getGender() == 1) {// 把int类型转成String
				studentExcel.setGender("男");
			} else {
				studentExcel.setGender("女");
			}
			if (student.getExamineeType() == 1) {
				studentExcel.setExamineeType("城镇应届");
			} else if (student.getExamineeType() == 2) {
				studentExcel.setExamineeType("农村应届");
			} else if (student.getExamineeType() == 3) {
				studentExcel.setExamineeType("城镇往届");
			} else {
				studentExcel.setExamineeType("农村往届");
			} 
			if (student.getGraduationType() == 1) {
				studentExcel.setGraduationType("高中");
			} else if (student.getGraduationType() == 2) {
				studentExcel.setGraduationType("中等师范");
			} else if (student.getGraduationType() == 3) {
				studentExcel.setGraduationType("中等专业学校");
			} else if (student.getGraduationType() == 4) {
				studentExcel.setGraduationType("职业高中学校");
			} else if(student.getGraduationType() == 5){
				studentExcel.setGraduationType("技工学校");
			} else{
				studentExcel.setGraduationType("其他学校");
			}
			if (student.getApplyType() == 1) {
				studentExcel.setApplyType("文史");
			} else if (student.getApplyType() == 2) {
				studentExcel.setApplyType("理工");
			} 
			if (student.getTestType() == 1) {
				studentExcel.setTestType("面试");
			} else {
				studentExcel.setTestType("机考");
			}
			if (student.getPromiseType() == 1) {
				studentExcel.setPromiseType("同意");
			} else {
				studentExcel.setPromiseType("不同意");
			}
			if (student.getPayType() == 1) {
				studentExcel.setPayType("已缴费");
			} else {
				studentExcel.setPayType("未缴费");
			}
			if (student.getTicketType() == 1) {
				studentExcel.setTicketType("已打印");
			} else {
				studentExcel.setTicketType("未打印");
			}
			if (student.getAdjustType() == 1) {
				studentExcel.setAdjustType("通过审核");
			} else {
				studentExcel.setAdjustType("未通过审核");
			}

			studentExcel.setId(student.getId());
			studentExcel.setHeight(student.getHeight());
			studentExcel.setEamineeId(student.getEamineeId());
			studentExcel.setCardId(student.getCardId());
			studentExcel.setName(student.getName());
			studentExcel.setPassword(student.getPassword());
			studentExcel.setBirthday(student.getBirthday());
			studentExcel.setImage(student.getImage());
			studentExcel.setNation(student.getNation());
			studentExcel.setLanguages(student.getLanguages());
			studentExcel.setProvinces(student.getProvinces());
			studentExcel.setRegions(student.getRegions());
			studentExcel.setCity(student.getCity());
			studentExcel.setTelephone(student.getTelephone());
			studentExcel.setGradSchool(student.getGradSchool());
			studentExcel.setIntheclass(student.getIntheclass());
			studentExcel.setVolunteerOne(student.getVolunteerOne());
			studentExcel.setVolunteerTwo(student.getVolunteerTwo());
			studentExcel.setVolunteerThree(student.getVolunteerThree());
			studentExcel.setVolunteerFour(student.getVolunteerFour());
			studentExcel.setVolunteerFive(student.getVolunteerFive());
			studentExcel.setMailingAddress(student.getMailingAddress());
			studentExcel.setPostalCode(student.getPostalCode());
			studentExcel.setAddressee(student.getAddressee());
			studentExcel.setAddrTele(student.getAddrTele());
			studentExcel.setRegisterTime(student.getRegisterTime());
			studentExcel.setPayTime(student.getPayTime());
			studentExcel.setTicketTime(student.getTicketTime());
			studentExcel.setPromiseTime(student.getPromiseTime());
			studentExcel.setLanguages(student.getLanguages());
			lists.add(studentExcel);
		}

		List<ExcelBean> excel = new ArrayList<>();
		Map<Integer, List<ExcelBean>> map = new LinkedHashMap<>();
		XSSFWorkbook xssfWorkbook = null;
		// 设置标题栏
		excel.add(new ExcelBean("学生序号", "id", 0));
		excel.add(new ExcelBean("报考序号", "height", 0));
		excel.add(new ExcelBean("考生号", "eamineeId", 0));
		excel.add(new ExcelBean("身份证号", "cardId", 0));
		excel.add(new ExcelBean("姓名", "name", 0));
		excel.add(new ExcelBean("审核号", "password", 0));
		excel.add(new ExcelBean("生日", "birthday", 0));
		excel.add(new ExcelBean("照片", "image", 0));
		excel.add(new ExcelBean("性别", "gender", 0));
		excel.add(new ExcelBean("民族", "nation", 0));
		excel.add(new ExcelBean("外语语种", "languages", 0));
		excel.add(new ExcelBean("省", "provinces", 0));
		excel.add(new ExcelBean("地区", "regions", 0));
		excel.add(new ExcelBean("城市", "city", 0));
		excel.add(new ExcelBean("手机号", "telephone", 0));
		excel.add(new ExcelBean("毕业学校", "gradSchool", 0));
		excel.add(new ExcelBean("所在班级", "intheclass", 0));
		excel.add(new ExcelBean("考生类别", "examineeType", 0));
		excel.add(new ExcelBean("毕业类别", "graduationType", 0));
		excel.add(new ExcelBean("报考类别", "applyType", 0));
		excel.add(new ExcelBean("综合素质测试", "testType", 0));
		excel.add(new ExcelBean("第一志愿", "volunteerOne", 0));
		excel.add(new ExcelBean("第二志愿", "volunteerTwo", 0));
		excel.add(new ExcelBean("第三志愿", "volunteerThree", 0));
		excel.add(new ExcelBean("第四志愿", "volunteerFour", 0));
		excel.add(new ExcelBean("第五志愿", "volunteerFive", 0));
		excel.add(new ExcelBean("审核状态", "adjustType", 0));
		excel.add(new ExcelBean("邮寄地址", "mailingAddress", 0));
		excel.add(new ExcelBean("邮政编码", "postalCode", 0));
		excel.add(new ExcelBean("收件人", "addressee", 0));
		excel.add(new ExcelBean("收件人电话", "addrTele", 0));
		excel.add(new ExcelBean("承诺书状态", "promiseType", 0));
		excel.add(new ExcelBean("缴费状态", "payType", 0));
		excel.add(new ExcelBean("准考证打印状态", "ticketType", 0));
		excel.add(new ExcelBean("注册时间", "registerTime", 0));
		excel.add(new ExcelBean("缴费时间", "payTime", 0));
		excel.add(new ExcelBean("打印准考证时间", "ticketTime", 0));
		excel.add(new ExcelBean("承诺时间", "promiseTime", 0));
		map.put(0, excel);
		String sheetName = studentData;
		// 调用ExcelUtil的方法
		xssfWorkbook = ExcelUtil.createExcelFile(StudentExcel.class, lists, map, sheetName);
		return xssfWorkbook;
	}
	/**
	 * 注册验证(访问外部地址)
	 * 
	 * @return
	 * @throws Exception
	 */
	public String Url(Student student, HttpServletRequest request) throws Exception {
		final String POST_URL = "http://221.9.255.60:8080/WS_KSXX/WS_KSXX.asmx";
		try {
			String xmlString = StudentXmlSp.getXmlStudent(student);
			byte[] xmlData = xmlString.getBytes("UTF-8");
			URL url = new URL(POST_URL);
			HttpURLConnection connection = (HttpURLConnection) url.openConnection();
			connection.setDoOutput(true);
			connection.setDoInput(true);
			connection.setRequestMethod("POST");
			connection.setUseCaches(false);
			connection.setInstanceFollowRedirects(false);
			connection.setRequestProperty("Content-Type", "text/xml;charset=utf-8");
			connection.setRequestProperty("Content-length", String.valueOf(xmlData.length));
			connection.connect();
			DataOutputStream dataout = new DataOutputStream(connection.getOutputStream());
			dataout.write(xmlData);
			dataout.flush();
			dataout.close();
			BufferedReader bf = new BufferedReader(new InputStreamReader(connection.getInputStream(), "UTF-8"));
			String line;
			StringBuilder sb = new StringBuilder(); // 用来存储响应数据
			while ((line = bf.readLine()) != null) {// 循环读取流,若不到结尾处
				sb.append(line).append(System.getProperty("line.separator"));
			}
			bf.close(); //关闭流
			connection.disconnect(); //销毁连接
			String dsb = sb.substring(0, sb.indexOf("<?xml"));// 截取所需json
			JSONObject jsonObject = JSONObject.fromObject(dsb);
			System.out.println("jsonObject====" + jsonObject);
			String image_path = null;
			String imgUrl = "";
			String Base64eamineeId = "";
			String eamineeId = "";
			String name = "";
			String applyType = "";
			String graduationType = "";
			String examineeType = "";
			String code = jsonObject.getString("e");
			if(code.equals("1"))
				return "5";
			JSONArray jsonArray = jsonObject.getJSONArray("info");//解析jsonObject
			for (int i = 0; i < jsonArray.size(); i++) {
				JSONObject obj = jsonArray.getJSONObject(i);
				imgUrl = obj.getString("KSZPURL");
				Base64eamineeId = obj.getString("KSH");
				name = obj.getString("XM");
				eamineeId = Base64.getFromBase64(Base64eamineeId);
				applyType = obj.getString("BKKLDM");
				graduationType = obj.getString("BYLBDM");
				examineeType = obj.getString("KSLBDM");
			}
			if(examineeType.equals("false"))
				return "6";
			if(name.equals("false"))
				return "2";
			if(applyType.equals("false"))
				return "3";
			if(graduationType.equals("false"))
				return "4";
			if (student.getEamineeId().equals(eamineeId)){
				image_path = FileUpload.download(imgUrl, eamineeId, request);
				return image_path;
			}else{
				return "1";
			}
			
		} catch (Exception e) {
			System.out.println("信息验证有误");
			return null;
		}
	}

	/**
	 * 信息审核(批量)
	 */
	public void examineStudent(List list) {
		Student studentx = new Student();
		studentx.setAdjustType(1);
		List max = getStudnets(studentx);
		int a = max.size();
		for (int i = 0; i < list.size(); i++) {
			Student student = new Student();
			String id = (String) list.get(i);
			String examines = Utils.examine(++a);
			student.setId(id);
			student.setAdjustType(1);
			student.setPassword(examines);
			studentMapper.updateByPrimaryKeySelective(student);
		}
	}

	/**
	 * 下载图片文件夹到本地
	 * 
	 * @throws Exception
	 */
	public void downloads(HttpServletRequest request, HttpServletResponse response,String path,String fileName) throws Exception {
		String dir = request.getSession().getServletContext().getRealPath("/");// 总路径
		String zipPath = "zipTmp/";
		String sourceFilePath = dir + path;
		String zipFilePath = dir + zipPath;
		File zipFile = new File(zipFilePath);
		if (!zipFile.exists()) {
			zipFile.mkdirs();
		}
		boolean flag = FileToZip.fileToZip(sourceFilePath, zipFilePath, fileName);
		if (flag) {
			System.out.println("文件打包成功!");
		} else {
			System.out.println("文件打包失败!");
		}
		FileUpload.downloadFileByOutputStream(response, request, zipFilePath, fileName);
	}
	public String JudgeIdRepeat(){
		String id=null;
		int i=0;
		for(;i==0;){
	      id = Utils.get5Radom();
			if(studentMapper.selectByPrimaryKey(id)==null){
				i=1;
			}
		}
		return id;
	}
}
